package dao.impl;

import core.BaseDao;
import dao.WorkerDao;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import pojo.Department;
import pojo.FindWorkerParam;
import pojo.Worker;
import util.DruidUtil;
import util.MyMapListHandler;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class WorkerDaoImpl extends BaseDao implements WorkerDao {

    public Connection connection;

    public WorkerDaoImpl() {
        connection = DruidUtil.getConnection();
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    public Connection getConnection() {
        try {
            if (connection.isClosed()) {
                connection = DruidUtil.getConnection();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * 员工登录方法
     *
     * @param workerLoginName 员工登录名
     * @param workerPassword  员工密码
     * @return
     */
    @Override
    public Worker login(String workerLoginName, String workerPassword) {
        try {
            sql = "SELECT * FROM worker WHERE worker_login_name=? AND worker_password=? AND worker_is_deleted=0";
            return queryRunner.query(sql, new BeanHandler<>(Worker.class, rowProcessor), workerLoginName, workerPassword);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 判断登录名是否存在
     *
     * @param loginName
     * @return
     */
    @Override
    public Boolean findByLoginName(String loginName) {
        try {
            sql = "SELECT * FROM worker WHERE worker_login_name=? AND worker_is_deleted=0";
            if (queryRunner.query(sql, new BeanHandler<>(Worker.class, rowProcessor), loginName) == null) {
                return false;
            }
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return true;
    }

    /**
     * 根据条件查找员工
     *
     * @param findWorkerParam
     * @return
     */
    @Override
    public List<Worker> findWorkByParam(FindWorkerParam findWorkerParam) {
        try {
            List<String> params = new ArrayList<>();
            sql = "SELECT * FROM worker,department WHERE worker_is_deleted=? AND worker_id != 1 AND worker.worker_department_code=department.department_code";
            produceSql(findWorkerParam, params);

            sql += " ORDER BY worker.worker_id DESC LIMIT ?,?";

            List<Worker> workerList = new ArrayList<>();
            if (params.size() == 4) {
                List<Map<String, Object>> query = queryRunner.query(sql, new MyMapListHandler(), findWorkerParam.getWorkerIsDeleted(), params.get(0), params.get(1), params.get(2), params.get(3), (findWorkerParam.getPage() - 1) * findWorkerParam.getLimit(), findWorkerParam.getLimit());
                return getWorkers(workerList, query);
            } else if (params.size() == 3) {
                List<Map<String, Object>> query = queryRunner.query(sql, new MyMapListHandler(), findWorkerParam.getWorkerIsDeleted(), params.get(0), params.get(1), params.get(2), (findWorkerParam.getPage() - 1) * findWorkerParam.getLimit(), findWorkerParam.getLimit());
                return getWorkers(workerList, query);
            } else if (params.size() == 2) {
                List<Map<String, Object>> query = queryRunner.query(sql, new MyMapListHandler(), findWorkerParam.getWorkerIsDeleted(), params.get(0), params.get(1), (findWorkerParam.getPage() - 1) * findWorkerParam.getLimit(), findWorkerParam.getLimit());
                return getWorkers(workerList, query);
            } else if (params.size() == 1) {
                List<Map<String, Object>> query = queryRunner.query(sql, new MyMapListHandler(), findWorkerParam.getWorkerIsDeleted(), params.get(0), (findWorkerParam.getPage() - 1) * findWorkerParam.getLimit(), findWorkerParam.getLimit());
                return getWorkers(workerList, query);
            } else {
                List<Map<String, Object>> query = queryRunner.query(sql, new MyMapListHandler(), findWorkerParam.getWorkerIsDeleted(), (findWorkerParam.getPage() - 1) * findWorkerParam.getLimit(), findWorkerParam.getLimit());
                return getWorkers(workerList, query);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }


        return new ArrayList<>();
    }

    /**
     * 判断sql语句
     *
     * @param findWorkerParam
     * @param params
     */
    private void produceSql(FindWorkerParam findWorkerParam, List<String> params) {
        if (findWorkerParam.getWorkerName() != null && !findWorkerParam.getWorkerName().isEmpty()) {
            sql += " AND worker_name LIKE ?";
            params.add("%" + findWorkerParam.getWorkerName() + "%");
        }
        if (findWorkerParam.getWorkerGender() != null && findWorkerParam.getWorkerGender() != 0) {
            sql += " AND worker_gender = ?";
            params.add(String.valueOf(findWorkerParam.getWorkerGender()));
        }
        if (findWorkerParam.getWorkerIdCard() != null && !findWorkerParam.getWorkerIdCard().isEmpty()) {
            sql += " AND worker_id_card LIKE ?";
            params.add("%" + findWorkerParam.getWorkerIdCard() + "%");
        }
        if (findWorkerParam.getWorkerDepartmentCode() != null && findWorkerParam.getWorkerDepartmentCode() != 0) {
            sql += " AND  worker_department_code = ?";
            params.add(String.valueOf(findWorkerParam.getWorkerDepartmentCode()));
        }
    }

    /**
     * 将查询到的worker和department对象数据组装为workerList
     *
     * @param workerList
     * @param query
     * @return
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     */
    static List<Worker> getWorkers(List<Worker> workerList, List<Map<String, Object>> query) throws IllegalAccessException, InvocationTargetException {
        for (Map<String, Object> objectMap : query) {
            Worker worker = new Worker();
            Department department = new Department();
            BeanUtils.populate(worker, objectMap);
            BeanUtils.populate(department, objectMap);
            worker.setDepartment(department);
            workerList.add(worker);
        }
        return workerList;
    }

    /**
     * 修改员工密码
     *
     * @param workerId
     * @param password
     * @return
     */
    @Override
    public Integer updateWorkerPassword(String workerId, String password) {
        try {
            sql = "UPDATE worker SET worker_password=? WHERE worker_id = ?";
            return queryRunner.update(sql, password, workerId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 根据员工电话号码修改密码
     *
     * @param workerPhone
     * @param resetPassword
     * @return
     */
    @Override
    public Integer updateWorkerPasswordByWorkerPhone(String workerPhone, String resetPassword) {
        try {
            sql = "UPDATE worker SET worker_password=? WHERE worker_phone = ?";
            return queryRunner.update(sql, resetPassword, workerPhone);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    /**
     * 根据员工登录名增加员工登录错误次数
     *
     * @param workerLoginName
     * @return
     */
    @Override
    public Integer addWorekErrorloginTimesByWorkerLoginName(String workerLoginName) {
        try {
            sql = "UPDATE worker SET worker_errorlogin_times=worker_errorlogin_times+1 WHERE worker_login_name = ? ";
            return queryRunner.update(sql, workerLoginName);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    /**
     * 根据员工登录名重置员工登录错误次数
     *
     * @param workerLoginName
     * @return
     */
    @Override
    public Integer resetWorekErrorloginTimesByWorkerLoginName(String workerLoginName) {
        try {
            sql = "UPDATE worker SET worker_errorlogin_times=0 WHERE worker_login_name = ? ";
            return queryRunner.update(sql, workerLoginName);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    /**
     * 根据员工登录名获取登录错误次数
     *
     * @param workerLoginName
     * @return
     */
    @Override
    public Integer findLoginerrorTimesByWorkerLoginName(String workerLoginName) {
        try {
            sql = "SELECT worker_errorlogin_times FROM worker WHERE worker_login_name=?";
            return (Integer) queryRunner.query(sql, new ScalarHandler(), workerLoginName);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return -1;
    }

    /**
     * 检查电话号码是否存在
     * @param workerPhone
     * @return
     */
    @Override
    public Boolean checkPhoneExist(String workerPhone) {
        try {
            sql = "SELECT count(1) FROM worker WHERE worker_phone = ?";
            Integer count = Integer.valueOf(queryRunner.query(sql, new ScalarHandler<>(), workerPhone).toString());
            if (count !=0 ){
                return true;
            }
            return false;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 根据id查找员工
     *
     * @param workerId
     * @return
     */
    @Override
    public Worker findWorkerById(String workerId) {
        try {
            sql = "SELECT * FROM worker WHERE worker_id=?";
            return queryRunner.query(sql, new BeanHandler<>(Worker.class, rowProcessor), workerId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new Worker();
    }

    /**
     * 根据条件查找员工的个数
     *
     * @param findWorkerParam
     * @return
     */
    @Override
    public Integer countFindWorkByParam(FindWorkerParam findWorkerParam) {
        try {
            List<String> params = new ArrayList<>();
            sql = "SELECT count(1) FROM worker,department WHERE worker_is_deleted=? AND worker_id != 1 AND worker.worker_department_code=department.department_code";
            produceSql(findWorkerParam, params);

            if (params.size() == 4) {
                return Integer.valueOf(queryRunner.query(sql, new ScalarHandler<>(), findWorkerParam.getWorkerIsDeleted(), params.get(0), params.get(1), params.get(2), params.get(3)).toString());
            } else if (params.size() == 3) {
                return Integer.valueOf(queryRunner.query(sql, new ScalarHandler<>(), findWorkerParam.getWorkerIsDeleted(), params.get(0), params.get(1), params.get(2)).toString());
            } else if (params.size() == 2) {
                return Integer.valueOf(queryRunner.query(sql, new ScalarHandler<>(), findWorkerParam.getWorkerIsDeleted(), params.get(0), params.get(1)).toString());
            } else if (params.size() == 1) {
                return Integer.valueOf(queryRunner.query(sql, new ScalarHandler<>(), findWorkerParam.getWorkerIsDeleted(), params.get(0)).toString());
            } else {
                return Integer.valueOf(queryRunner.query(sql, new ScalarHandler<>(), findWorkerParam.getWorkerIsDeleted()).toString());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }


    /**
     * 添加员工
     *
     * @param worker
     * @return
     */
    @Override
    public Integer add(Worker worker) {
        try {
            sql = "INSERT INTO worker(worker_name,worker_gender,worker_login_name,worker_password,worker_phone,worker_salary,worker_id_card,worker_create_time,worker_update_time,worker_department_code) VALUES (?,?,?,?,?,?,?,?,?,?)";
            return queryRunner.update(sql,
                    worker.getWorkerName(),
                    worker.getWorkerGender(),
                    worker.getWorkerLoginName(),
                    worker.getWorkerPassword(),
                    worker.getWorkerPhone(),
                    worker.getWorkerSalary(),
                    worker.getWorkerIdCard(),
                    worker.getWorkerCreateTime(),
                    worker.getWorkerUpdateTime(),
                    worker.getWorkerDepartmentCode()
            );
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 获取登录员工所有信息
     *
     * @param workerLoginName
     * @return
     */
    @Override
    public Worker workerDetail(String workerLoginName) {
        try {
            sql = "SELECT * FROM worker,department WHERE worker_login_name=? AND worker.worker_department_code=department.department_code";
            List<Map<String, Object>> queryList = queryRunner.query(sql, new MyMapListHandler(), workerLoginName);
            Worker worker = new Worker();
            Department department = new Department();
            BeanUtils.populate(worker, queryList.get(0));
            BeanUtils.populate(department, queryList.get(0));
            worker.setDepartment(department);
            return worker;
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 通过员工登录名查找员工
     *
     * @param workerLoginName 员工登录名
     * @return
     */
    @Override
    public Worker findWorkerByName(String workerLoginName) {
        try {
            sql = "SELECT * FROM worker WHERE worker_login_name=? AND worker_is_deleted=0";
            return queryRunner.query(sql, new BeanHandler<>(Worker.class, rowProcessor), workerLoginName);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 通过id查找员工
     *
     * @param workerId
     * @param connection
     * @return
     */
    public Worker findWorkerById(String workerId, Connection connection) {
        try {
            sql = "SELECT * FROM worker WHERE worker_id=?";
            return queryRunner.query(connection, sql, new BeanHandler<>(Worker.class, rowProcessor), workerId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new Worker();
    }
}
